USE CAFEDB
GO


-----------------------------INSERT PROCEDURE------------------------------
--AREA
CREATE PROC INSERTAREA(@AREAID varchar(20),@AREANAME nvarchar(50))
AS
	BEGIN
		INSERT INTO [CAFEDB].[dbo].[AREA]
           ([AREAID]
           ,[AREANAME])
     VALUES
           (@AREAID,@AREANAME)
	END
GO
--DEPARTMENT
CREATE PROC INSERTDEPARTMENT(@DEPARTMENTID varchar(20)
           ,@DEPARTMENTNAME nvarchar(100))
AS
	BEGIN
		INSERT INTO [CAFEDB].[dbo].[DEPARTMENT]
           ([DEPARTMENTID]
           ,[DEPARTMENTNAME])
     VALUES
           (@DEPARTMENTID
           ,@DEPARTMENTNAME)
	END
GO
--DETAIL_GROUPUSER_ROLE
CREATE PROC INSERTDETAILGROUPUSERROLE(@GROUPUSERCODE VARCHAR (50)
           ,@ROLECODE varchar(50),@PERMIT BIT)
AS
	BEGIN
		INSERT INTO [CAFEDB].[dbo].[DETAIL_GROUPUSER_ROLE]
           ([GROUPUSERCODE]
           ,[ROLECODE],PERMIT)
     VALUES
           (@GROUPUSERCODE 
           ,@ROLECODE
		   ,@PERMIT)
	END
GO
--EMPLOYER

CREATE  PROC INSERTEMPLOYER(
            @FIRSTNAME nvarchar(50)
           ,@MIDDLENAME nvarchar(50)
           ,@LASTNAME nvarchar(50)
           ,@FULLNAME nvarchar(150)
           ,@DOB datetime
           ,@IDCARD varchar(9)
           ,@ADDRESS nvarchar(100)
           ,@DEPARTMENTID varchar(20)
           ,@DATEWORK datetime)
AS
	BEGIN
		INSERT INTO [CAFEDB].[dbo].[EMPLOYER]
           ([FIRSTNAME]
           ,[MIDDLENAME]
           ,[LASTNAME]
           ,[FULLNAME]
           ,[DOB]
           ,[IDCARD]
           ,[ADDRESS]
           ,[DATEWORK]
           ,[DEPARTMENTID])
     VALUES
           (@FIRSTNAME
           ,@MIDDLENAME
           ,@LASTNAME
           ,@FULLNAME
           ,CONVERT(DATETIME,@DOB,103) 
           ,@IDCARD
           ,@ADDRESS
           ,CONVERT(DATETIME,@DATEWORK,103)
           ,@DEPARTMENTID)
	END
GO

--EXPORT
CREATE PROC INSERTEXPORT(@EXPORTID varchar(10)
           ,@TABLEID INT
           ,@EMPLOYERID varchar(20)
           ,@TABLEDISCOUNT float
		   ,@VAT float
		   ,@SERVICE float)
AS
	BEGIN
		INSERT INTO [CAFEDB].[dbo].[EXPORT]
           ([EXPORTID]
           ,[TABLEID]
           ,[EMPLOYERID]
           ,[TABLEDISCOUNT]
		   ,[VAT]
		   ,[SERVICE])
     VALUES
           (@EXPORTID 
           ,@TABLEID 
           ,@EMPLOYERID 
           ,@TABLEDISCOUNT
		   ,@VAT
		   ,@SERVICE)
	END
GO
--EXPORTDETAIL
CREATE PROC INSERTEXPORTDETAIL(@EXPORTID varchar(10)
           ,@ITEMID int
           ,@QUANTITY int
           ,@PRICE float
           ,@ITEMDISCOUNT float)
AS
	BEGIN
		INSERT INTO [CAFEDB].[dbo].[EXPORTDETAIL]
           ([EXPORTID]
           ,[ITEMID]
           ,[QUANTITY]
           ,[PRICE]
           ,[ITEMDISCOUNT])
     VALUES
           (@EXPORTID
           ,@ITEMID
           ,@QUANTITY
           ,@PRICE
           ,@ITEMDISCOUNT)
	END
GO
--GROUPUSER
CREATE PROC INSERTGROUPUSER(@GROUPUSERNAME nvarchar(100)
           ,@GROUPUSERDESCRIPTION nvarchar(100)
           ,@USERCREATE varchar(30))
AS
	BEGIN
		INSERT INTO [CAFEDB].[dbo].[GROUPUSER]
           ([GROUPUSERNAME]
           ,[GROUPUSERDESCRIPTION]
           ,[USERCREATE]
           ,[DATECREATE]
           ,[USERUPDATE]
           ,[LASTUPDATE])
     VALUES
           (@GROUPUSERNAME
           ,@GROUPUSERDESCRIPTION
           ,@USERCREATE
           ,GETDATE()
           ,@USERCREATE
           ,GETDATE())

	INSERT INTO DETAIL_GROUPUSER_ROLE( ROLECODE, GROUPUSERCODE, PERMIT )
SELECT  [ROLE].ROLECODE, (SELECT TOP 1 GROUPUSERCODE FROM GROUPUSER ORDER BY GROUPUSERCODE DESC), 0
FROM    [ROLE]
	END
GO

CREATE PROC INSERTGROUPUSERNOTROLE(@GROUPUSERNAME nvarchar(100)
           ,@GROUPUSERDESCRIPTION nvarchar(100)
           ,@USERCREATE varchar(30))
AS
	BEGIN
		INSERT INTO [CAFEDB].[dbo].[GROUPUSER]
           ([GROUPUSERNAME]
           ,[GROUPUSERDESCRIPTION]
           ,[USERCREATE]
           ,[DATECREATE]
           ,[USERUPDATE]
           ,[LASTUPDATE])
     VALUES
           (@GROUPUSERNAME
           ,@GROUPUSERDESCRIPTION
           ,@USERCREATE
           ,GETDATE()
           ,@USERCREATE
           ,GETDATE())

	
	END
GO
--IMPORT
CREATE PROC INSERTIMPORT(@IMPORTID varchar(10)
           ,@DATEIMPORT datetime
           ,@ISDEBT bit
           ,@PAID float
           ,@NOTE nvarchar(500)
           ,@EMPLOYERID varchar(20))
AS
	BEGIN
		INSERT INTO [CAFEDB].[dbo].[IMPORT]
           ([IMPORTID]
           ,[DATEIMPORT]
           ,[ISDEBT]
           ,[PAID]
           ,[NOTE]
           ,[EMPLOYERID])
     VALUES
           (@IMPORTID
           ,@DATEIMPORT
           ,@ISDEBT
           ,@PAID
           ,@NOTE
           ,@EMPLOYERID)
	END
GO
--IMPORTDETAIL
CREATE PROC INSERTIMPORTDETAIL(@IMPORTID varchar(10)
           ,@MATERIALID int
           ,@QUANTITY float
           ,@UNITID int)
AS
	BEGIN
		INSERT INTO [CAFEDB].[dbo].[IMPORTDETAIL]
           ([IMPORTID]
           ,[MATERIALID]
           ,[QUANTITY]
           ,[UNITID])
     VALUES
           (@IMPORTID
           ,@MATERIALID
           ,@QUANTITY
           ,@UNITID)
	END
GO
--ITEM
CREATE  PROC  INSERTITEM(
           @ITEMNAME nvarchar(50)
           ,@ITEMTYPEID int
           ,@ISDRINK bit
           ,@PRICE float
           ,@SHORTCUT VARCHAR(50))
AS
	BEGIN
		INSERT INTO [CAFEDB].[dbo].[ITEM]
           (
           [ITEMNAME]
           ,[ITEMTYPEID]
           ,[ISDRINK]
           ,[PRICE]
           ,[SHORTCUT])
     VALUES
           (
           @ITEMNAME
           ,@ITEMTYPEID
           ,@ISDRINK
           ,@PRICE
           ,@SHORTCUT)
	END
GO
--ITEMTYPE
CREATE  PROC INSERTITEMTYPE(
           @ITEMTYPENAME nvarchar(50))
AS
	BEGIN
		INSERT INTO [CAFEDB].[dbo].[ITEMTYPE]
           (
           [ITEMTYPENAME])
     VALUES
           (
           @ITEMTYPENAME)
	END
GO
--MATERIAL
CREATE PROC INSERTMATERIAL(@MATERIALNAME nvarchar(50)
           ,@QUANTITY float
           ,@UNITID int)
AS
	BEGIN
		INSERT INTO [CAFEDB].[dbo].[MATERIAL]
           (
           [MATERIALNAME]
           ,[QUANTITY]
           ,[UNITID])
     VALUES
           (
           @MATERIALNAME
           ,@QUANTITY
           ,@UNITID)
	END
GO
--GROUP ROLE
CREATE  PROC INSERTGROUPROLE(@GROUPROLECODE VARCHAR(50),
           @GROUPROLENAME nvarchar(100)
           ,@USERCREATE varchar(30))
         
        
AS
	BEGIN
		INSERT INTO [CAFEDB].[dbo].[GROUPROLE]
           (
		   [GROUPROLECODE],
           [GROUPROLENAME]
           ,[USERCREATE]
           ,[DATECREATE]
           ,[USERUPDATE]
           ,[LASTUPDATE])
     VALUES
           (
		   @GROUPROLECODE,
           @GROUPROLENAME
           ,@USERCREATE
           ,GETDATE()
           ,@USERCREATE
           ,GETDATE())
	END
GO
--ROLE
CREATE PROC INSERTROLE(@ROLECODE varchar(50)
           ,@ROLENAME nvarchar(100)
           ,@GROUPROLECODE VARCHAR(50)
           ,@USERCREATE varchar(30))
         
        
AS
	BEGIN
		INSERT INTO [CAFEDB].[dbo].[ROLE]
           ([ROLECODE]
           ,[ROLENAME]
           ,[GROUPROLECODE]
           ,[USERCREATE]
           ,[DATECREATE]
           ,[USERUPDATE]
           ,[LASTUPDATE])
     VALUES
           (@ROLECODE
           ,@ROLENAME
           ,@GROUPROLECODE
           ,@USERCREATE
           ,GETDATE()
           ,@USERCREATE
           ,GETDATE())
	END
GO
--TABLE
CREATE PROC INSERTTABLE(
           @TABLENAME nvarchar(50)
           ,@AREAID varchar(20))
AS
	BEGIN
		INSERT INTO [CAFEDB].[dbo].[TABLE]
           (
           [TABLENAME]
           ,[AREAID])
     VALUES
           (
           @TABLENAME
           ,@AREAID)
	END
GO
--UNIT
CREATE PROC INSERTUNIT(
           @UNITNAME nvarchar(50))
AS
	BEGIN
		INSERT INTO [CAFEDB].[dbo].[UNIT]
           (
           [UNITNAME])
     VALUES
           (@UNITNAME)
	END
GO
--USERACCOUNT
CREATE PROC INSERTUSERACCOUNT(@USERNAME varchar(30)
           ,@PASSWORD varchar(32)
           ,@FIRSTNAME nvarchar(50)
           ,@MIDDLENAME nvarchar(50)
           ,@LASTNAME nvarchar(50)
           ,@FULLNAME nvarchar(150)
           ,@DOB datetime
           ,@EMAIL varchar(100)
           ,@IDCARD varchar(20)
           ,@ISADMIN bit
           ,@MOBILEPHONE varchar(20)
           ,@HOMEPHONE varchar(20)
           ,@GROUPUSERCODE int
           ,@EMPLOYERID varchar(20))
AS
	BEGIN
		INSERT INTO [CAFEDB].[dbo].[USERACCOUNT]
           ([USERNAME]
           ,[PASSWORD]
           ,[FIRSTNAME]
           ,[MIDDLENAME]
           ,[LASTNAME]
           ,[FULLNAME]
           ,[DOB]
           ,[EMAIL]
           ,[IDCARD]
           ,[DATECREATE]
           ,[LASTUPDATE]
           ,[ISADMIN]
           ,[MOBILEPHONE]
           ,[HOMEPHONE]
           ,[GROUPUSERCODE]
           ,[EMPLOYERID])
     VALUES
           (@USERNAME
           ,@PASSWORD
           ,@FIRSTNAME
           ,@MIDDLENAME
           ,@LASTNAME
           ,@FULLNAME
           ,@DOB
           ,@EMAIL
           ,@IDCARD
           ,getdate()
           ,getdate()
           ,@ISADMIN
           ,@MOBILEPHONE
           ,@HOMEPHONE
           ,@GROUPUSERCODE
           ,@EMPLOYERID)
	END
GO
--WAGE
CREATE PROC INSERTWAGE(@WAGEID varchar(20)
           ,@WAGENAME nvarchar(50)
           ,@PRICE float
           ,@DEPARTMENTID varchar(20))
AS
	BEGIN
		INSERT INTO [CAFEDB].[dbo].[WAGE]
           ([WAGEID]
           ,[WAGENAME]
           ,[PRICE]
           ,[DEPARTMENTID])
     VALUES
           (@WAGEID
           ,@WAGENAME
           ,@PRICE
           ,@DEPARTMENTID)
	END
GO

GO	


